package com.topisv.tms.tools;

import java.beans.PropertyDescriptor;
import java.io.BufferedOutputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.antlr.stringtemplate.StringTemplate;
import org.antlr.stringtemplate.StringTemplateGroup;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


/**
 * Excel导出，使用StringTemplate
 */
public class ExcelStUtil {
    
    private static final Logger logger = LoggerFactory.getLogger(ExcelStUtil.class);
    
    // 每个sheet行数60000
    private static int maxRowNum = 60000;
    
    /**
     * 把文件输出到OutputStream
     * 
     * @param outputStream
     *            输出流
     * @param sheetNamePre
     *            sheet名称前缀
     * @param pageTitle
     *            Excel标题
     * @param titles
     *            Excel每列的标题
     * @param dataList
     *            待写入的数据
     */
    public static void export(OutputStream outputStream, String sheetNamePre, String pageTitle, String[] titles, List<Object[]> dataList) {
        logger.debug("begin to export to OutputStream...");
        long startTime = System.currentTimeMillis();
        
        StringTemplateGroup stGroup = new StringTemplateGroup("excelTemplate");
        stGroup.setFileCharEncoding("UTF-8");
        
        // 写入excel文件头部信息
        StringTemplate head = stGroup.getInstanceOf("st/excel/head");
        head.setAttribute("createdTime", DateFormatUtils.format(new Date(), "yyyy-MM-dd'T'HH:mm:ss'Z'"));
        
        PrintWriter writer = new PrintWriter(new BufferedOutputStream(outputStream));
        writer.print(head.toString());
        //writer.flush();  //remove 20160714 lianghe.yuan
        
        // 计算sheet数量
        int totalRowNum = dataList.size();
        int sheetNum = totalRowNum / maxRowNum + (totalRowNum % maxRowNum == 0 ? 0 : 1);
        
        int columnLength = titles.length;
        
        for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) {
            Worksheet worksheet = new ExcelStUtil.Worksheet();
            worksheet.setPageTitle(pageTitle);
            worksheet.setSheet(sheetNamePre + " - " + (sheetIndex + 1));
            worksheet.setTitles(titles);
            worksheet.setMergeAcross(columnLength - 1);
            worksheet.setColumnNum(columnLength);
            worksheet.setRowNum(maxRowNum + 2);
            
            StringTemplate body = stGroup.getInstanceOf("st/excel/body");
            List<Row> rowList = new ArrayList<Row>(maxRowNum);
            
            int startIndex = sheetIndex * maxRowNum;
            int endIndex = Math.min((sheetIndex + 1) * maxRowNum - 1, totalRowNum - 1);
            
            for (int rowIndex = startIndex; rowIndex <= endIndex; rowIndex++) {
                Row row = new ExcelStUtil.Row();
                List<String> result = new ArrayList<String>(columnLength);
                
                Object[] values = dataList.get(rowIndex);
                for (int columnIndex = 0; columnIndex < columnLength; columnIndex++) {
                    Object value = values[columnIndex];
                    
                    if (value == null) {
                        result.add("");
                    } else {
                        if (value instanceof Date) {
                            result.add(DateFormatUtils.format((Date)value, "yyyy-MM-dd HH:mm:ss"));
                        } else {
                            result.add(value.toString().replace("<", "&lt;").replace(">", "&gt;")); // 转义，否则excel打开报错。wps打开时报错行以下所有记录不显示
                        }
                    }
                }
                
                row.setResult(result);
                rowList.add(row);
            }
            
            worksheet.setRows(rowList);
            body.setAttribute("worksheet", worksheet);
            
            rowList = null;
            worksheet = null;
            
            writer.print(body.toString());
            //writer.flush(); //remove 20160714 lianghe.yuan
            
            rowList = null;
            worksheet = null;
            body = null;
            
            logger.debug("生成Excel第{}个sheet.", sheetIndex + 1);
        }
        
        // 写入excel文件尾部
        writer.print("</Workbook>");
        writer.flush();
        writer.close();
        
        long endTime = System.currentTimeMillis();
        logger.debug("生成Excel结束.耗时：{} ''", (endTime - startTime));
    }
    
    /**
     * 利用反射机制对象保存到excel
     * 
     * @param outputStream
     *            输出流
     * @param sheetNamePre
     *            sheet名称前缀
     * @param pageTitle
     *            Excel标题
     * @param titles
     *            Excel每列的标题
     * @param dataList
     *            待写入的数据
     * @throws Exception
     */
    public static void exportModel(OutputStream outputStream, String sheetNamePre, String pageTitle, String[] titles, List<?> dataList)
        throws Exception
    {
        if (dataList.size() < 1)
            return;
        logger.debug("begin to export to OutputStream...");
        long startTime = System.currentTimeMillis();
        
        StringTemplateGroup stGroup = new StringTemplateGroup("excelTemplate");
        stGroup.setFileCharEncoding("UTF-8");
        
        // 写入excel文件头部信息
        StringTemplate head = stGroup.getInstanceOf("st/excel/head");
        head.setAttribute("createdTime", DateFormatUtils.format(new Date(), "yyyy-MM-dd'T'HH:mm:ss'Z'"));
        
        PrintWriter writer = new PrintWriter(new BufferedOutputStream(outputStream));
        writer.print(head.toString());
        writer.flush();
        
        // 计算sheet数量
        int totalRowNum = dataList.size();
        int sheetNum = totalRowNum / maxRowNum + (totalRowNum % maxRowNum == 0 ? 0 : 1);
        
        int columnLength = titles.length;
        
        for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) {
            Worksheet worksheet = new ExcelStUtil.Worksheet();
            worksheet.setPageTitle(pageTitle);
            worksheet.setSheet(sheetNamePre + " - " + (sheetIndex + 1));
            worksheet.setTitles(titles);
            worksheet.setMergeAcross(columnLength - 1);
            worksheet.setColumnNum(columnLength);
            worksheet.setRowNum(maxRowNum + 2);
            
            StringTemplate body = stGroup.getInstanceOf("st/excel/body");
            List<Row> rowList = new ArrayList<Row>(maxRowNum);
            
            int startIndex = sheetIndex * maxRowNum;
            int endIndex = Math.min((sheetIndex + 1) * maxRowNum - 1, totalRowNum - 1);
            
            for (int rowIndex = startIndex; rowIndex <= endIndex; rowIndex++) {
                Row row = new ExcelStUtil.Row();
                List<String> result = new ArrayList<String>(columnLength);
                
                Object obj = dataList.get(rowIndex);
                Class<?> clazz = obj.getClass();
                Field[] fields = obj.getClass().getDeclaredFields();
                for (Field field : fields) {
                    PropertyDescriptor pd = new PropertyDescriptor(field.getName(), clazz);
                    Method getMethod = pd.getReadMethod();// 获得get方法
                    Object value = getMethod.invoke(obj);// 执行get方法返回一个Object
                    if (value == null) {
                        result.add("");
                    } else {
                        if (value instanceof Date) {
                            result.add(DateFormatUtils.format((Date)value, "yyyy-MM-dd HH:mm:ss"));
                        } else {
                            result.add(value.toString().replace("<", "&lt;").replace(">", "&gt;")); // 转义，否则excel打开报错。wps打开时报错行以下所有记录不显示
                        }
                    }
                }
                
                row.setResult(result);
                rowList.add(row);
            }
            
            worksheet.setRows(rowList);
            body.setAttribute("worksheet", worksheet);
            
            rowList = null;
            worksheet = null;
            
            writer.print(body.toString());
            writer.flush();
            
            rowList = null;
            worksheet = null;
            body = null;
            
            logger.debug("生成Excel第{}个sheet.", sheetIndex + 1);
        }
        
        // 写入excel文件尾部
        writer.print("</Workbook>");
        writer.flush();
        writer.close();
        
        long endTime = System.currentTimeMillis();
        logger.debug("生成Excel结束.耗时：{} ''", (endTime - startTime));
    }
    
    public static void main(String[] args)
        throws Exception
    {
        String pageTitle = "库存明细";
        String[] titles = {"序号", "仓库", "库位", "SKU", "数量"};
        List<Object[]> dataList = new ArrayList<Object[]>();
        
        for (int i = 0; i < 60000 * 1 + 10; i++) {
            dataList.add(new Object[] {i + 1, "杭州仓", "LOC00" + (i + 1), "SKU00" + (i + 1), (i + 1)});
        }
        
        long t1 = System.currentTimeMillis();
        
        // 写入到本地文件
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        export(byteArrayOutputStream, "", pageTitle, titles, dataList);
        
        File file = new File("D:\\\\库存明细.xls");
        OutputStream output = new FileOutputStream(file);
        BufferedOutputStream bufferedOutput = new BufferedOutputStream(output);
        bufferedOutput.write(byteArrayOutputStream.toString().getBytes("UTF-8"));
        bufferedOutput.flush();
        bufferedOutput.close();
        
        // ExcelUtils.writeExcel(titles, "库存明细2.xls",pageTitle, "1" , dataList); // 生成excel
        
        long t2 = System.currentTimeMillis();
        
        logger.info("耗时: {}''", t2 - t1);
    }
    
    static class Row {
        
        private List<String> result;
        
        public List<String> getResult() {
            return result;
        }
        
        public void setResult(List<String> result) {
            this.result = result;
        }
    }
    
    static class Worksheet {
        
        private String pageTitle; // sheet标题
        
        private String sheet; // sheet名称
        
        private String[] titles; // 每列标题
        
        private List<Row> rows; // 数据行集合
        
        private int columnNum; // 列数
        
        private int mergeAcross; // 标题行合并行数
        
        private int rowNum; // 行数
        
        public String getSheet() {
            return sheet;
        }
        
        public void setSheet(String sheet) {
            this.sheet = sheet;
        }
        
        public List<Row> getRows() {
            return rows;
        }
        
        public void setRows(List<Row> rows) {
            this.rows = rows;
        }
        
        public int getColumnNum() {
            return columnNum;
        }
        
        public void setColumnNum(int columnNum) {
            this.columnNum = columnNum;
        }
        
        public int getRowNum() {
            return rowNum;
        }
        
        public void setRowNum(int rowNum) {
            this.rowNum = rowNum;
        }
        
        public String[] getTitles() {
            return titles;
        }
        
        public void setTitles(String[] titles) {
            this.titles = titles;
        }
        
        public String getPageTitle() {
            return pageTitle;
        }
        
        public void setPageTitle(String pageTitle) {
            this.pageTitle = pageTitle;
        }
        
        public int getMergeAcross() {
            return mergeAcross;
        }
        
        public void setMergeAcross(int mergeAcross) {
            this.mergeAcross = mergeAcross;
        }
        
    }
    
}
